package com.dd.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.LockMode;
import org.springframework.context.ApplicationContext;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.dd.entity.DdCount;
import com.dd.entity.DdSms;
import com.dd.entity.DdUser;

/**
 * A data access object (DAO) providing persistence and search support for
 * DdUser entities. Transaction control of the save(), update() and delete()
 * operations can directly support Spring container-managed transactions or they
 * can be augmented to handle user-managed Spring transactions. Each of these
 * methods provides additional information for how to configure it for the
 * desired type of transaction control.
 * 
 * @see com.dd.entity.DdUser
 * @author MyEclipse Persistence Tools
 */

public class DdUserDAO extends HibernateDaoSupport {
	private final String name = "ztshop";
	private final String pas = "www.ztshop.com";
	
	/*
	 * 取新订单信息
	 */
	private void SendMsg(String oid){
		
		try {
			StringBuffer sbf = new StringBuffer();
			sbf.append("select Name,Gender,Tel,Remark from merchantorder where id="+oid+";");
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;databasename=DanDing";
			Connection conn = DriverManager.getConnection(url, name, pas);
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			if (rs.next()) {
				DdSms dsms = new DdSms();
				dsms.setSendmsg("【蛋订网】"+rs.getString("Name")+""+(rs.getString("Gender").equals("1")?"先生":"女士")+"您的订单:"+oid+" 商家已经确认。位置:\""+rs.getString("Remark")+"\"。(享受优惠不开票)");
				dsms.setOrderid(Integer.parseInt(oid));
				dsms.setOrderstatus(2);
				dsms.setTypes(2);
				dsms.setStatus(0);
				dsms.setPhone(rs.getString("Tel"));
				getHibernateTemplate().saveOrUpdate(dsms);
			}
			rs.close();
			stat.close();
			conn.close();
		} catch (Exception re) {
			re.printStackTrace();
		}
		
	}
	public String getNewOrder(){
		StringBuffer sbf1 = new StringBuffer();
		try {
			StringBuffer sbf = new StringBuffer();
			sbf.append("select a.id as oid,a.status as status,b.Name as sname,b.Tel as stel,a.Name as uname,a.Gender as sex,a.Tel as utel,a.UserNum as unum,a.ArrivedTime as arrivedtime,a.SeatReq as req,a.MsgToMer as msg");
			sbf.append(" from merchantorder a,Merchant b where a.MerchantId=b.id and (a.Status=1 or a.status=2) order by a.id desc;");
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;databasename=DanDing";
			Connection conn = DriverManager.getConnection(url, name, pas);
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			int count=0;
			while (rs.next()) {
				if(count!=0){
					sbf1.append(",");
				}
				count++;
				sbf1.append("{\"oid\":\""+rs.getString("oid")+"\"");
				sbf1.append(",\"status\":\""+rs.getString("status")+"\"");
				sbf1.append(",\"sname\":\""+rs.getString("sname")+"\"");
				sbf1.append(",\"stel\":\""+rs.getString("stel")+"\"");
				sbf1.append(",\"uname\":\""+rs.getString("uname")+"\"");
				sbf1.append(",\"sex\":\""+rs.getString("sex")+"\"");
				sbf1.append(",\"utel\":\""+rs.getString("utel")+"\"");
				sbf1.append(",\"unum\":\""+rs.getString("unum")+"\"");
				sbf1.append(",\"arrivedtime\":\""+rs.getString("arrivedtime")+"\"");
				sbf1.append(",\"req\":\""+rs.getString("req")+"\"");
				sbf1.append(",\"msg\":\""+rs.getString("msg")+"\"");
				sbf1.append("}");
			}
			rs.close();
			stat.close();
			conn.close();
		} catch (Exception re) {
			re.printStackTrace();
		}
		return sbf1.toString();
	}
	
	/*
	 * 更新订单信息
	 */
	public boolean updateNewOrder(String oid,String remark,int status){
		
		try {
			StringBuffer sbf = new StringBuffer();
			sbf.append("update  merchantorder set status="+status+",remark='"+remark+"' where id="+oid+";");
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;databasename=DanDing";
			Connection conn = DriverManager.getConnection(url, name, pas);
			Statement stat = conn.createStatement();
			stat.executeUpdate(sbf.toString());
			if(status==2){//是确认订单的,增加短信发送
				this.SendMsg(oid);
			}
		} catch (Exception re) {
			re.printStackTrace();
			return false;
		}
		return true;
	}
	public String getOldOrder(String sid,String uid,String fromtime,String totime){
		StringBuffer sbf1 = new StringBuffer();
		try {
			StringBuffer sbf = new StringBuffer();
			sbf.append("select a.id as oid,a.status as status,b.Name as sname,b.Tel as stel,a.Name as uname,a.Gender as sex,a.Tel as utel,a.UserNum as unum,a.OrderTime as otime,a.ArrivedTime as arrivedtime,a.remark as remark ");
			sbf.append(" from merchantorder a,Merchant b where a.MerchantId=b.id and (a.Status=2 or a.Status=3) ");
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;databasename=DanDing";
			Connection conn = DriverManager.getConnection(url, name, pas);
			Statement stat = conn.createStatement();
			if(sid!=null&&!sid.equals("")){
				sbf.append(" and b.id="+sid);
			}
			if(uid!=null&&!uid.equals("")){
				sbf.append(" and a.DdUserid="+uid);
			}
			if(fromtime!=null&&!fromtime.equals("")){
				sbf.append(" and a.OrderTime>'"+fromtime+"'");
			}
			if(totime!=null&&!totime.equals("")){
				sbf.append(" and a.OrderTime<'"+totime+"'");
			}
			sbf.append(" order by a.id desc;");
			ResultSet rs = stat.executeQuery(sbf.toString());
			int count=0;
			while (rs.next()) {
				if(count!=0){
					sbf1.append(",");
				}
				count++;
				sbf1.append("{\"oid\":\""+rs.getString("oid")+"\"");
				sbf1.append(",\"status\":\""+rs.getString("status")+"\"");
				sbf1.append(",\"sname\":\""+rs.getString("sname")+"\"");
				sbf1.append(",\"stel\":\""+rs.getString("stel")+"\"");
				sbf1.append(",\"uname\":\""+rs.getString("uname")+"\"");
				sbf1.append(",\"sex\":\""+rs.getString("sex")+"\"");
				sbf1.append(",\"utel\":\""+rs.getString("utel")+"\"");
				sbf1.append(",\"unum\":\""+rs.getString("unum")+"\"");
				sbf1.append(",\"arrivedtime\":\""+rs.getString("arrivedtime")+"\"");
				sbf1.append(",\"otime\":\""+rs.getString("otime")+"\"");
				sbf1.append(",\"remark\":\""+rs.getString("remark")+"\"");
				sbf1.append("}");
			}
			rs.close();
			stat.close();
			conn.close();
		} catch (Exception re) {
			re.printStackTrace();
		}
		return sbf1.toString();
	}
	
	
	//上面是sqlserver2008方法
	public void sqlTest() {
		try {
			StringBuffer sbf = new StringBuffer();
			sbf.append("select * from dduser;");
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;databasename=DanDing";
			Connection conn = DriverManager.getConnection(url, name, pas);
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sbf.toString());
			while (rs.next()) {
				System.out.println(rs.getInt("id"));
			}
			rs.close();
			stat.close();
			conn.close();
		} catch (Exception re) {
			re.printStackTrace();
		}
	}

	private static final Log log = LogFactory.getLog(DdUserDAO.class);
	// property constants
	public static final String PHONE = "phone";
	public static final String PASSWORD = "password";
	public static final String EMAIL = "email";
	public static final String NAME = "name";
	public static final String SEX = "sex";
	public static final String AGE = "age";
	public static final String OCCUPATION = "occupation";
	public static final String COMPANY = "company";
	public static final String ADDRESS = "address";
	public static final String ADDRESS2 = "address2";
	public static final String AERA2 = "aera2";
	public static final String ROAD2 = "road2";
	public static final String INCOME = "income";
	public static final String AERA = "aera";
	public static final String ROAD = "road";
	public static final String CREDIT = "credit";

	protected void initDao() {
		// do nothing
	}

	public void saveOrUpdateDC(DdCount transientInstance) {
		log.debug("saving DdUser instance");
		try {
			getHibernateTemplate().saveOrUpdate(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public List findDCByDate(int uid, long begDate, long endDate, String url) {
		try {
			String queryString = "from DdCount where 1=1";
			String whereString = "";
			if (uid != -1) {
				whereString = whereString + " and uid=" + uid;
			}
			if (begDate != 0) {
				whereString = whereString + " and date>" + begDate;
			}
			if (endDate != 0) {
				whereString = whereString + " and date<" + endDate;
			}
			if (url != null) {
				whereString = whereString + " and url like '%" + url + "%'";
			}
			List li = getHibernateTemplate().find(queryString);
			return li;
		} catch (Exception re) {
			re.printStackTrace();
			return new ArrayList();
		}
	}

	public List findDCByDate(Object logid) {
		try {
			String queryString = "from DdUser as model where model.logid= ?";
			List li = getHibernateTemplate().find(queryString, logid);
			return li;
		} catch (Exception re) {
			re.printStackTrace();
			return new ArrayList();
		}
	}

	public void saveOrUpdate(DdUser transientInstance) {
		log.debug("saving DdUser instance");
		try {
			getHibernateTemplate().saveOrUpdate(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public List findAll() {
		log.debug("finding all DdUser instances");
		try {
			String queryString = "from DdUser";
			return getHibernateTemplate().find(queryString);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

	public void delete(DdUser persistentInstance) {
		log.debug("deleting DdUser instance");
		try {
			getHibernateTemplate().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	public DdUser findById(java.lang.Integer id) {
		log.debug("getting DdUser instance with id: " + id);
		try {
			DdUser instance = (DdUser) getHibernateTemplate().get(
					"com.dd.entity.DdUser", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	public List findByProperty(String propertyName, Object value) {
		log.debug("finding DdUser instance with property: " + propertyName
				+ ", value: " + value);
		try {
			String queryString = "from DdUser as model where model."
					+ propertyName + "= ?";
			return getHibernateTemplate().find(queryString, value);
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	public DdUser findByPhone(Object phone) {
		try {

			List li = getHibernateTemplate().find(
					"from DdUser where logid='" + phone + "' or phone ='"
							+ phone + "'");
			if (li.size() != 0) {
				return (DdUser) li.get(0);
			} else {
				return null;
			}
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;

		}
	}

	public DdUser findBylogid(Object logid) {
		try {
			String queryString = "from DdUser as model where model.logid= ?";
			List li = getHibernateTemplate().find(queryString, logid);
			if (li.size() != 0) {
				return (DdUser) li.get(0);
			} else {
				return null;
			}
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;

		}
	}
}